import xlrd
import json
import os
from xlutils.copy import copy

class OperateExcel():
    #返回工作表行数
    def nrows(self,excelDir,sheetname):
        workbook = xlrd.open_workbook(excelDir,formatting_info = True)
        worksheet = workbook.sheet_by_name(sheetname)
        nrows = worksheet.nrows
        return nrows

    #返回工作表列数
    def cols(self,excelDir,sheetname):
        workbook = xlrd.open_workbook(excelDir,formatting_info = True)
        worksheet = workbook.sheet_by_name(sheetname)
        cols = worksheet.ncols
        return cols

    #返回excel中所有参数
    def excel_all_param(self,excelDir,sheetname):
        workbook = xlrd.open_workbook(excelDir,formatting_info=True)
        worksheet = workbook.sheet_by_name(sheetname)
        nrows = worksheet.nrows
        listData = []

        for i in range(0,nrows-1):
            i = i + 1
            cellData = worksheet.row_values(i)
            listData.append(cellData)
        return listData

    #返回excel中的请求参数
    def request_param(self,excelDir,sheetname):
        workbook = xlrd.open_workbook(excelDir,formatting_info = True)
        worksheet = workbook.sheet_by_name(sheetname)
        nrows = worksheet.nrows
        request_param = []

        for i in range(0,nrows -1):
            i = i + 1
            cellData = worksheet.cell(i,6).value
            request_param.append(cellData)
        return request_param

if __name__ =="__main__":
    excel_path = os.path.join(os.path.join(os.path.dirname(os.path.split(os.path.realpath(__file__))[0]),"data"),"接口用例.xls")
    print(excel_path)
    a = json.loads(OperateExcel().excel_all_param(excelDir=excel_path,sheetname="更新key批次详情（批量增加兑换码批次）(restful接口)")[23][6])
    print(a,type(a))

